package com.example.database.dao.impl;

import com.example.database.dao.StudentDao;
import com.example.database.entity.*;
import com.example.database.enums.IdentificationType;
import com.example.database.enums.Status;
import com.example.database.util.MySqlConnector;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Repository
public class StudentDaoImpl implements StudentDao {

    private String sqlFindAdmissionFormByDept = "select * from admission_form where student_id in (select student_id from student where class_id in (select class_id from class where department_id = ?));";
    private String sqlFindAdmissionFormByClass = "select * from admission_form where student_id in (select student_id from student where class_id = ?);";
    private String sqlFindLeavingFormByDept = "select * from leaving_form where student_id in (select student_id from student where class_id in (select class_id from class where department_id = ?));";
    private String sqlFindLeavingFormByClass = "select * from leaving_form where student_id in (select student_id from student where class_id = ?);";


    public List<AdmissionForm> findAdmissionForm(Integer id,String sql) {
        List<AdmissionForm> admissionForms = new ArrayList<>();
        if (id == null) return admissionForms;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql1 = "select count(form_id) as num from health_form where student_id = ?";
            String sql3 = "select * from health_form where form_id = ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)) {
                try(PreparedStatement statement1 = conn.prepareStatement(sql1)) {
                    try(PreparedStatement statement2 = conn.prepareStatement(sql3)) {
                        if (id != 0) statement.setInt(1,id);
                        ResultSet resultSet = statement.executeQuery();
                        AdmissionForm admissionForm = new AdmissionForm();
                        int num = 0;
                        while (resultSet.next()){
                            String[] days = {"day1","day2","day3","day4","day5","day6","day6","day7"};
                            admissionForm.setForm_id(resultSet.getInt("form_id"));
                            admissionForm.setStudent_id(resultSet.getInt("student_id"));
                            admissionForm.setEnter_time(resultSet.getString("enter_time"));
                            admissionForm.setReason(resultSet.getString("reason"));
                            admissionForm.setRefuseReason(resultSet.getString("refuse_reason"));
                            admissionForm.setApprove_time(resultSet.getString("approve_time"));
                            admissionForm.setStatus(resultSet.getString("state"));
                            admissionForm.setRegTime(resultSet.getString("reg_time"));

                            statement1.setInt(1,resultSet.getInt("student_id"));
                            ResultSet resultSet1 = statement1.executeQuery();
                            if (resultSet1.next())num = Math.min(resultSet1.getInt("num"),7);
                            List<HealthForm> healthForms = new ArrayList<>();
                            for (int i =0;i<num;i++){
                                statement2.setInt(1,resultSet.getInt(days[i]));
                                ResultSet resultSet2 = statement2.executeQuery();
                                HealthForm healthForm = new HealthForm();

                                if (resultSet2.next()){
                                    healthForm.setForm_id(resultSet2.getInt("form_id"));
                                    healthForm.setTemperature(resultSet2.getFloat("temperature"));
                                    healthForm.setTime(resultSet2.getString("time"));
                                    healthForm.setLocation(resultSet2.getString("location"));
                                    healthForm.setStudent_id(resultSet2.getInt("student_id"));
                                    healthForms.add(healthForm);
                                }
                            }
                            admissionForm.setSeven_days_info(healthForms);
                            admissionForms.add(admissionForm);
                        }
                    }
                }
            }
            conn.close();
            return admissionForms;
        }catch (Exception e){
            e.printStackTrace();
            return admissionForms;
        }
    }
    public List<LeavingForm> findLeavingForm(Integer id,String sql) {
        List<LeavingForm> leavingForms = new ArrayList<>();
        if (id == null) return leavingForms;
        try {
            Connection conn= new MySqlConnector().getConnection();
            try(PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
                if (id != 0)preparedStatement.setInt(1,id);
                ResultSet resultSet = preparedStatement.executeQuery();
                LeavingForm leavingForm = new LeavingForm();
                while (resultSet.next()){
                    leavingForm.setForm_id(resultSet.getInt("form_id"));
                    leavingForm.setStudent_id(resultSet.getInt("student_id"));
                    leavingForm.setReason(resultSet.getString("reason"));
                    leavingForm.setRefuseReason(resultSet.getString("refuse_reason"));
                    leavingForm.setDestination(resultSet.getString("destination"));
                    leavingForm.setLeaving_time(resultSet.getString("leaving_time"));
                    leavingForm.setReturn_time(resultSet.getString("return_time"));
                    leavingForm.setWriteTime(resultSet.getString("write_time"));
                    leavingForm.setApproveTime(resultSet.getString("approve_time"));
                    leavingForm.setState(Status.valueOf(resultSet.getString("state")));
                    leavingForms.add(leavingForm);
                }
            }
            conn.close();
            return leavingForms;
        }catch (Exception e){
            e.printStackTrace();
            return leavingForms;
        }
    }

    @Override
    public Boolean addStudent(Student student) {
        if (student == null) return Boolean.FALSE;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "insert into student (student_id,password,name, phone, email," +
                    " address, building_num, room_num, identification_type, id_number," +
                    " status, total_time_out, campus_id, class_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
            try (PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,student.getStudent_id());
                statement.setString(2,student.getPassword());
                statement.setString(3,student.getName());
                statement.setString(4,student.getPhone());
                statement.setString(5,student.getEmail());
                statement.setString(6,student.getAddress());
                statement.setInt(7,student.getBuilding_num());
                statement.setInt(8,student.getRoom_num());
                statement.setString(9,student.getIdentification_type().toString());
                statement.setString(10,student.getId_number());
                statement.setString(11,Status.OUTSCHOOL.toString());
                statement.setInt(12,0);
                statement.setInt(13,student.getCampus_id());
                statement.setInt(14,student.getClass_id());
                if (statement.executeUpdate() <= 0)return Boolean.FALSE;
                conn.close();
                return Boolean.TRUE;
            }
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public Boolean deleteStudentById(Integer studentId) {
        if (studentId == null) return Boolean.FALSE;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "delete from student where student_id = ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)) {
                statement.setInt(1,studentId);
                if(statement.executeUpdate() <= 0) return Boolean.FALSE;
                conn.close();
                return Boolean.TRUE;
            }
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public Boolean updateStudent(Student student) {
        if (student == null) return Boolean.FALSE;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "update student set name = ?,phone = ?,address = ?,status = ?,total_time_out = ? where student_id = ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)) {
                statement.setString(1,student.getName());
                statement.setString(2,student.getPhone());
                statement.setString(3,student.getAddress());
                statement.setString(4,student.getStatus().toString());
                statement.setInt(5,student.getTotal_time_out());
                statement.setInt(6,student.getStudent_id());
                if (statement.executeUpdate() <= 0) return Boolean.FALSE;
            }
            conn.close();
            return Boolean.TRUE;
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public Student findStudentById(Integer studentId) {
        if (studentId == null) return null;
        Student student = new Student();
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select * from student where student_id = ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,studentId);
                ResultSet resultSet = statement.executeQuery();
                if (resultSet.next()){
                    student.setStudent_id(resultSet.getInt("student_id"));
                    student.setPassword(resultSet.getString("password"));
                    student.setName(resultSet.getString("name"));
                    student.setPhone(resultSet.getString("phone"));
                    student.setEmail(resultSet.getString("email"));
                    student.setAddress(resultSet.getString("address"));
                    student.setBuilding_num(resultSet.getInt("building_num"));
                    student.setRoom_num(resultSet.getInt("room_num"));
                    student.setIdentification_type(IdentificationType.valueOf(resultSet.getString("identification_type")));
                    student.setId_number(resultSet.getString("id_number"));
                    student.setStatus(Status.valueOf(resultSet.getString("status")));
                    student.setTotal_time_out(resultSet.getInt("total_time_out"));
                    student.setCampus_id(resultSet.getInt("campus_id"));
                    student.setClass_id(resultSet.getInt("class_id"));
                }
                conn.close();
                return student;
            }
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    @Override
    public ClassInstructor findClassInstructorByStudentId(Integer studentId) {
        // TODO
        return null;
    }

    @Override
    public List<Student> findStudentByCampus(Integer campusId) {
        List<Student> students = new ArrayList<>();
        if (campusId == null) return students;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select * from student where campus_id = ?;";
            try (PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,campusId);
                ResultSet resultSet = statement.executeQuery();
                while (resultSet.next()){
                    students.add(findStudentById(resultSet.getInt("student_id")));
                }
                conn.close();
                return students;
            }
        }catch (Exception e){
            e.printStackTrace();
            return students;
        }
    }
    // 是不是还需要提供一个 查找一个班级的同学
    @Override
    public List<InOutLog> findLogByCampus(Integer campusId) {
        List<InOutLog> inOutLogs = new ArrayList<>();
        if (campusId == null) return inOutLogs;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select * from in_out_log where student_id in (select student.student_id from student where campus_id = ?);";
            try (PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,campusId);
                ResultSet resultSet = statement.executeQuery();
                InOutLog inOutLog = new InOutLog();
                while (resultSet.next()){
                    inOutLog.setLog_id(resultSet.getInt("log_id"));
                    inOutLog.setStatus(Status.valueOf(resultSet.getString("status")));
                    inOutLog.setTime(resultSet.getString("time"));
                    inOutLog.setCampus_name(resultSet.getString("campus_name"));
                    inOutLog.setStudent_id(resultSet.getInt("student_id"));
                    inOutLogs.add(inOutLog);
                }
            }
            conn.close();
            return inOutLogs;
        }catch (Exception e){
            e.printStackTrace();
            return inOutLogs;
        }
    }

    public Status findAdmissionStateFormById(Integer id,Boolean type){
        if (id == null) return null;
        Status state = null;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select state from admission_form where form_id = ?;";
            if (!type) sql = "select state from leaving_form where form_id = ?;";
            try (PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,id);
                ResultSet resultSet = statement.executeQuery();
                while (resultSet.next()){
                    state = Status.valueOf(resultSet.getString("state"));
                }
            }
            conn.close();
            return state;
        }catch (Exception e){
            e.printStackTrace();
            return state;
        }
    }
    @Override
    public List<AdmissionForm> findAdmissionFormAll() {
        return findAdmissionForm(0,"select * from admission_form");
    }

    @Override
    public List<LeavingForm> findLeavingFormAll() {
        return findLeavingForm(0,"select * from leaving_form");
    }

    // TODO 查找一个学生的 进出校记录 过去n天

    @Override
    public List<AdmissionForm> findAdmissionFormByDepartment(Integer departmentId) {
        return findAdmissionForm(departmentId,sqlFindAdmissionFormByDept);
    }

    @Override
    public Boolean processAdmissionFormByDeptInstructor(Integer formId,boolean operation,String approveTime,String reason) {
        if (formId == null)return Boolean.FALSE;
        String sql = "update admission_form set  state = ?,approve_time = ?,refuse_reason = ? where form_id = ?;";
        try {
            Connection conn = new MySqlConnector().getConnection();
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                if (!operation){
                    statement.setString(1,Status.FAIL.toString());

                }else {
                    statement.setString(1,Status.PASS.toString());
                }
                statement.setString(2,approveTime);
                statement.setString(3,reason);
                statement.setInt(4,formId);
                if (statement.executeUpdate() <= 0) return Boolean.FALSE;
            }
            conn.close();
            return Boolean.TRUE;
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public List<LeavingForm> findLeavingFormByDepartment(Integer departmentId) {
        return findLeavingForm(departmentId,sqlFindLeavingFormByDept);
    }

    @Override
    public Boolean processLeavingFormByDeptInstructor(Integer formId,boolean operation,String approveTime,String reason) {
        if (formId == null)return Boolean.FALSE;
        String sql = "update leaving_form set  state = ?,approve_time = ?,refuse_reason = ? where form_id = ?;";
        try {
            Connection conn = new MySqlConnector().getConnection();
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                if (!operation){
                    statement.setString(1,Status.FAIL.toString());

                }else {
                    statement.setString(1,Status.PASS.toString());
                }
                statement.setString(2,approveTime);
                statement.setString(3,reason);
                statement.setInt(4,formId);
                if (statement.executeUpdate() <= 0) return Boolean.FALSE;
            }
            conn.close();
            return Boolean.TRUE;
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public List<AdmissionForm> findAdmissionFormByClass(Integer classId) {
        return findAdmissionForm(classId,sqlFindAdmissionFormByClass);
    }

    @Override
    public Boolean processAdmissionFormByClassInstructor(Integer formId,boolean operation,String approveTime,String reason) {
        if (formId == null || findAdmissionStateFormById(formId,Boolean.TRUE) == Status.FAIL || findAdmissionStateFormById(formId,Boolean.TRUE) == Status.PASS)return Boolean.FALSE;
        String sql = "update admission_form set  state = ?,approve_time = ?,refuse_reason = ? where form_id = ?;";
        try {
            Connection conn = new MySqlConnector().getConnection();
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                if (!operation){
                    statement.setString(1,Status.FAIL.toString());

                }else {
                    statement.setString(1,Status.INVIEW.toString());
                }
                statement.setString(2,approveTime);
                statement.setString(3,reason);
                statement.setInt(4,formId);
                if (statement.executeUpdate() <= 0) return Boolean.FALSE;
            }
            conn.close();
            return Boolean.TRUE;
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public List<LeavingForm> findLeavingFormByClass(Integer classId) {
        return findLeavingForm(classId,sqlFindLeavingFormByClass);
    }

    @Override
    public Boolean processLeavingFormByClassInstructor(Integer formId,boolean operation,String approveTime,String reason) {
        if (formId == null || findAdmissionStateFormById(formId,Boolean.FALSE) == Status.FAIL || findAdmissionStateFormById(formId,Boolean.FALSE) == Status.PASS)return Boolean.FALSE;
        String sql = "update leaving_form set  state = ?,approve_time = ?,refuse_reason = ? where form_id = ?;";
        try {
            Connection conn = new MySqlConnector().getConnection();
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                if (!operation){
                    statement.setString(1,Status.FAIL.toString());
                }else {
                    statement.setString(1,Status.INVIEW.toString());
                }
                statement.setString(2,approveTime);
                statement.setString(3,reason);
                statement.setInt(4,formId);
                if (statement.executeUpdate() <= 0) return Boolean.FALSE;
            }
            conn.close();
            return Boolean.TRUE;
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public Boolean addAdmissionForm(AdmissionForm admissionForm) {
        if (admissionForm == null)return Boolean.FALSE;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "insert into admission_form (form_id, student_id, day1, day2, day3, day4, day5, day6, day7, enter_time, reason, approve_time, state, reg_time) " +
                    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try(PreparedStatement statement = conn.prepareStatement(sql)) {
                statement.setInt(1,admissionForm.getForm_id());
                statement.setInt(2,admissionForm.getStudent_id());

                if(admissionForm.getSeven_days_info()!= null){
                    for (int i =0;i<admissionForm.getSeven_days_info().size();i++){
                        statement.setInt(i+3, admissionForm.getSeven_days_info().get(i).getForm_id());
                    }
                }
                else for (int i=0;i<7;i++){
                    statement.setInt(i+3,1);
                }
                statement.setString(10,admissionForm.getEnter_time());
                statement.setString(11,admissionForm.getReason());
                statement.setString(12,admissionForm.getApprove_time());
                statement.setString(13,Status.BEGIN.toString());
                System.out.println(admissionForm.toString());
                statement.setString(14,admissionForm.getRegTime());
                if (statement.executeUpdate() <= 0)return Boolean.FALSE;
            }


            conn.close();
            return Boolean.TRUE;
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public Boolean addLeavingForm(LeavingForm leavingForm) {
        if (leavingForm == null)return Boolean.FALSE;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "insert into leaving_form(form_id, student_id, reason, destination, leaving_time, return_time, write_time, approve_time, state) " +
                    "VALUES (?,?,?,?,?,?,?,?,?)";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,leavingForm.getForm_id());
                statement.setInt(2,leavingForm.getStudent_id());
                statement.setString(3,leavingForm.getReason());
                statement.setString(4,leavingForm.getDestination());
                statement.setString(5,leavingForm.getLeaving_time());
                statement.setString(6,leavingForm.getReturn_time());
                statement.setString(7,leavingForm.getWriteTime());
                statement.setString(8,leavingForm.getApproveTime());
                statement.setString(9,Status.BEGIN.toString());
                if (statement.executeUpdate() <= 0)return Boolean.FALSE;
            }
            conn.close();
            return Boolean.TRUE;
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public Boolean addHealthForm(HealthForm healthForm) {
        if (healthForm == null)return Boolean.FALSE;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "insert into health_form(form_id, temperature, time, location, student_id) values (?,?,?,?,?);";
            try(PreparedStatement statement = conn.prepareStatement(sql)) {
                statement.setInt(1,healthForm.getForm_id());
                statement.setFloat(2,healthForm.getTemperature());
                statement.setDate(3, Date.valueOf(healthForm.getTime()));
                statement.setString(4, healthForm.getLocation());
                statement.setInt(5,healthForm.getStudent_id());
                if (statement.executeUpdate() <= 0)return Boolean.FALSE;
            }
            conn.close();
            return Boolean.TRUE;
        }catch (Exception e){
            e.printStackTrace();
            return Boolean.FALSE;
        }
    }

    @Override
    public List<HealthForm> findNDaysHealthInfo(Integer studentId, Integer nDays) {
        List<HealthForm> healthForms = new ArrayList<>();
        if (studentId == null || nDays <= 0)return healthForms;
        try {
            Connection conn = new MySqlConnector().getConnection();
            // TODO
            // 如果有系统时间 则修改这一部分
            String sql = "select * from health_form where TIMESTAMPDIFF(DAY,time,CURDATE()) < ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,nDays);
                ResultSet resultSet = statement.executeQuery();
                HealthForm healthForm = new HealthForm();
                while (resultSet.next()){
                    healthForm.setForm_id(resultSet.getInt("form_id"));
                    healthForm.setStudent_id(resultSet.getInt("student_id"));
                    healthForm.setTemperature(resultSet.getFloat("temperature"));
                    healthForm.setTime(resultSet.getString("time"));
                    healthForm.setLocation(resultSet.getString("location"));
                    healthForms.add(healthForm);
                }
            }
            conn.close();
            return healthForms;
        }catch (Exception e){
            e.printStackTrace();
            return healthForms;
        }
    }

    @Override
    public List<AdmissionForm> findAdmissionFormByStudentId(Integer studentId, Status state) {
        List<AdmissionForm> admissionForms = new ArrayList<>();
        System.out.println(studentId + " and " + state);
        if (studentId == null || state == null) return admissionForms;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select * from admission_form where student_id = ? and state = ? ;";
            String sql1 = "select count(form_id) as num from health_form where student_id = ?";
            String sql3 = "select * from health_form where form_id = ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)) {
                try(PreparedStatement statement1 = conn.prepareStatement(sql1)){
                    try(PreparedStatement statement2 = conn.prepareStatement(sql3)){
                        statement.setInt(1,studentId);
                        statement.setString(2,state.toString());
                        ResultSet resultSet = statement.executeQuery();
                        AdmissionForm admissionForm = new AdmissionForm();
                        int num = 0;
                        while (resultSet.next()){
                            String[] days = {"day1","day2","day3","day4","day5","day6","day6","day7"};
                            admissionForm.setForm_id(resultSet.getInt("form_id"));
                            admissionForm.setStudent_id(resultSet.getInt("student_id"));
                            admissionForm.setEnter_time(resultSet.getString("enter_time"));
                            admissionForm.setReason(resultSet.getString("reason"));
                            admissionForm.setApprove_time(resultSet.getString("approve_time"));
                            admissionForm.setStatus(resultSet.getString("state"));
                            admissionForm.setRegTime(resultSet.getString("reg_time"));

                            statement1.setInt(1,resultSet.getInt("student_id"));
                            ResultSet resultSet1 = statement1.executeQuery();
                            if (resultSet1.next())num = (Math.min(resultSet1.getInt("num"), 7));
                            List<HealthForm> healthForms = new ArrayList<>();
                            for (int i =0;i<num;i++){
                                statement2.setInt(1,resultSet.getInt(days[i]));
                                ResultSet resultSet2 = statement2.executeQuery();
                                HealthForm healthForm = new HealthForm();

                                if (resultSet2.next()){
                                    healthForm.setForm_id(resultSet2.getInt("form_id"));
                                    healthForm.setTemperature(resultSet2.getFloat("temperature"));
                                    healthForm.setTime(resultSet2.getString("time"));
                                    healthForm.setLocation(resultSet2.getString("location"));
                                    healthForm.setStudent_id(resultSet2.getInt("student_id"));
                                    healthForms.add(healthForm);
                                }
                            }
                            admissionForm.setSeven_days_info(healthForms);
                            admissionForms.add(admissionForm);
                        }
                    }
                }
            }
            conn.close();
            return admissionForms;
        }catch (Exception e){
            e.printStackTrace();
            return admissionForms;
        }
    }

    @Override
    public List<LeavingForm> findLeavingFormByStudentId(Integer studentId, Status state) {
        List<LeavingForm> leavingForms = new ArrayList<>();
        if (studentId == null || state == null) return leavingForms;
        try {
            Connection conn= new MySqlConnector().getConnection();
            String sql = "select * from leaving_form where student_id = ? and state = ?;";
            try (PreparedStatement preparedStatement = conn.prepareStatement(sql)){
                preparedStatement.setInt(1,studentId);
                preparedStatement.setString(2,state.toString());
                ResultSet resultSet = preparedStatement.executeQuery();
                LeavingForm leavingForm = new LeavingForm();
                while (resultSet.next()){
                    leavingForm.setForm_id(resultSet.getInt("form_id"));
                    leavingForm.setStudent_id(resultSet.getInt("student_id"));
                    leavingForm.setReason(resultSet.getString("reason"));
                    leavingForm.setDestination(resultSet.getString("destination"));
                    leavingForm.setLeaving_time(resultSet.getString("leaving_time"));
                    leavingForm.setReturn_time(resultSet.getString("return_time"));
                    leavingForm.setWriteTime(resultSet.getString("write_time"));
                    leavingForm.setApproveTime(resultSet.getString("approve_time"));
                    leavingForm.setState(Status.valueOf(resultSet.getString("state")));
                    leavingForms.add(leavingForm);
                }
            }
            conn.close();
            return leavingForms;
        }catch (Exception e){
            e.printStackTrace();
            return leavingForms;
        }
    }

    @Override
    public List<Student> findMostAdmissionStudentAll() {
        List<Student> students = new ArrayList<>();
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select max(all num) as maxnum from (select student_id,count(distinct form_id) as num from admission_form group by student_id order by num) as a;";
            String sql1 = "select student_id from (select student_id,count(distinct form_id) as num from admission_form group by student_id order by num) as b where num = ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)) {
                try(PreparedStatement statement1 = conn.prepareStatement(sql1)) {
                    ResultSet resultSet = statement.executeQuery();
                    resultSet.next();
                    int max = resultSet.getInt("maxnum");
                    statement1.setInt(1,max);
                    ResultSet resultSet1 = statement1.executeQuery();
                    while (resultSet1.next()){
                        students.add(findStudentById(resultSet1.getInt("student_id")));
                    }
                }
            }
            conn.close();
            return students;
        }catch (Exception e){
            e.printStackTrace();
            return students;
        }
    }

    @Override
    public List<Student> findMostAdmissionStudentByDept(Integer deptId) {
        List<Student> students = new ArrayList<>();
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select student_id from (select student_id,count(form_id) as num from " +
                    "(select student_id,form_id from admission_form where student_id in " +
                    "(select student_id from student natural join " +
                    "(select class_id from class where department_id = ?) as a)) as b group by student_id) as temp where num in" +
                    "(select max(all num) as maxnum from (select count(form_id) as num from " +
                    "(select student_id,form_id from admission_form where student_id in " +
                    "(select student_id from student natural join " +
                    "(select class_id from class where department_id = ?) as a)) as b group by student_id) as c);";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,deptId);
                statement.setInt(2,deptId);
                ResultSet resultSet = statement.executeQuery();
                while (resultSet.next()){
                    students.add(findStudentById(resultSet.getInt("student_id")));
                }
            }
            conn.close();
            return students;
        }catch (Exception e){
            e.printStackTrace();
            return students;
        }
    }

    @Override
    public List<Student> findMostAdmissionStudentByClass(Integer classId) {
        List<Student> students = new ArrayList<>();
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select student_id from " +
                    "(select student_id,count(form_id) as num from " +
                    "(select student_id,form_id from admission_form where student_id in " +
                    "(select student_id from student where class_id = ?))as a group by student_id) as b where num in " +
                    "(select max(all num) as maxnum from " +
                    "(select student_id,count(form_id) as num from " +
                    "(select student_id,form_id from admission_form where student_id in " +
                    "(select student_id from student where class_id = ?))as c group by student_id)as d);";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setInt(1,classId);
                statement.setInt(2,classId);
                ResultSet resultSet = statement.executeQuery();
                while (resultSet.next()){
                    students.add(findStudentById(resultSet.getInt("student_id")));
                }
            }
            conn.close();
            return students;
        }catch (Exception e){
            e.printStackTrace();
            return students;
        }
    }

    @Override
    public String OutOfSchoolTimeOneYearByStudentId(Integer studentId) {
        DateTimeFormatter simpleFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss");
        if (studentId == null) return null;
        String result = "";
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select time from in_out_log where student_id = ? and status = ? and datediff(now(),time) < 365  order by time;";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                int minutes = 0;
                // 获取一个学生的进校记录
                statement.setInt(1,studentId);
                statement.setString(2,Status.INSCHOOL.toString());
                ResultSet resultSet = statement.executeQuery();
                // 获取一个学生的出校记录
                statement.setString(2,Status.OUTSCHOOL.toString());
                ResultSet resultSet1 = statement.executeQuery();
                resultSet.next();

                while (resultSet.next()){
                    resultSet1.next();
                    Date out = resultSet1.getDate("time");
                    Date in = resultSet.getDate("time");
                    minutes += (int)((in.getTime() - out.getTime()) / (1000*60));
                }
                if (resultSet1.next()){
                    LocalDateTime temp = LocalDateTime.now();
                    Date cur = Date.valueOf(temp.format(simpleFormat));
                    minutes += (int)((cur.getTime() - resultSet1.getDate("time").getTime()) / (1000*60));
                }
                int hour = minutes / 60;
                minutes = minutes % 60;
                result = hour + "小时" + minutes + "分钟";
            }
            conn.close();
            return result;
        }catch (Exception e){
            e.printStackTrace();
            return result;
        }
    }

    @Override
    public Integer regOutButInSchoolNum() {
        Integer num = 0;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select count(distinct student_id) as num from student where status = ? and student_id in (select student_id from leaving_form where state = ?);";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setString(1,Status.INSCHOOL.toString());
                statement.setString(2,Status.BEGIN.toString());
                ResultSet resultSet = statement.executeQuery();
                if (resultSet.next()){
                    num = resultSet.getInt("num");
                }
                statement.setString(2,Status.INVIEW.toString());
                ResultSet resultSet1 = statement.executeQuery();
                if (resultSet1.next()){
                    num += resultSet1.getInt("num");
                }
            }
            conn.close();
            return num;
        }catch (Exception e){
            e.printStackTrace();
            return num;
        }
    }

    @Override
    public List<Student> regOutButInSchoolStudentInfo() {
        List<Student> students = new ArrayList<>();
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select student_id from student where status = ? and student_id in (select student_id from leaving_form where state = ?);";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setString(1,Status.INSCHOOL.toString());
                statement.setString(2,Status.BEGIN.toString());
                ResultSet resultSet = statement.executeQuery();
                while (resultSet.next()){
                    students.add(findStudentById(resultSet.getInt("student_id")));
                }
                statement.setString(2,Status.INVIEW.toString());
                ResultSet resultSet1 = statement.executeQuery();
                while (resultSet1.next()){
                    students.add(findStudentById(resultSet1.getInt("student_id")));
                }
            }
            conn.close();
            return students;
        }catch (Exception e){
            e.printStackTrace();
            return students;
        }
    }

    @Override
    public Integer outSchoolStudentNum() {
        Integer num = 0;
        try {
            Connection conn = new MySqlConnector().getConnection();
            String sql = "select count(student_id) as num from student where status = ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setString(1,Status.OUTSCHOOL.toString());
                ResultSet resultSet = statement.executeQuery();
                if (resultSet.next()){
                    num = resultSet.getInt("num");
                }
            }
            conn.close();
            return num;
        }catch (Exception e){
            e.printStackTrace();
            return num;
        }
    }

    @Override
    public List<Student> outSchoolStudentInfo() {
        List<Student> students = new ArrayList<>();
        try {
            Connection conn= new MySqlConnector().getConnection();
            String sql = "select student_id from student where status = ?;";
            try(PreparedStatement statement = conn.prepareStatement(sql)){
                statement.setString(1,Status.OUTSCHOOL.toString());
                ResultSet resultSet = statement.executeQuery();
                while (resultSet.next()){
                    students.add(findStudentById(resultSet.getInt("student_id")));
                }
            }
            conn.close();
            return students;
        }catch (Exception e){
            e.printStackTrace();
            return students;
        }
    }
}
